﻿using GPS.MODELS;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using GPS.DataAccessLayer;
using AutoMapper;
using PagedList;
using GPS.ENTITYS;
using GPS.COMMON;
using System.IO;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using Oracle.ManagedDataAccess.Client;
using System.Data;
using Microsoft.AspNet.Identity;
using NPOI.HSSF.UserModel;
using OfficeOpenXml;
using System.Web.UI.WebControls;
using System.Web.UI;
using System.Threading.Tasks;

namespace GPS.Controllers.GPS_CapPhep
{
    public class GPS_TheoDoiCapPhepPhuongTienController : Controller
    {
        DbContextHelper<GPS_NVContext> _db = Singleton<DbContextHelper<GPS_NVContext>>.Inst;
        public GPS.MODELS.ApplicationUser getCurrentUser()
        {
            ApplicationDbContext dbUser = new ApplicationDbContext();
            string userId = User.Identity.GetUserId();
            ApplicationUser _currentUser = dbUser.Users.Find(userId);
            return _currentUser;
        }
        public void initialCategorySearchAction(ViewModelCAPPHEP_PHUONGTIEN SearchModel)
        {
            string DVGTVT_MA = this.getCurrentUser().MA_TINHTP;
            ViewBag.checkuser = DVGTVT_MA;
            string getdvgtvt_Ma = GPS.DAL.Check_User.CheckUser(this.getCurrentUser().MA_TINHTP, SearchModel.pDVGTVT_MA);
            SearchModel.pDVGTVT_MA = getdvgtvt_Ma;
        }

        #region Index
        public ActionResult Index(ViewModelCAPPHEP_PHUONGTIEN SearchModel, int? currentPage)
        {
            initialCategorySearchAction(SearchModel);
            List<CrudModelCAPPHEP_PHUONGTIEN> model = new List<CrudModelCAPPHEP_PHUONGTIEN>();
            model = GetListData(SearchModel);
            ViewBag.TotalRow = model.Count();
            int icount = 1;
            foreach (var currentEntity in model)
            {
                currentEntity.STT = icount.ToString();
                icount++;
            }
            if (SearchModel.btnExport == "In sổ theo dõi")
            {
                string tempAdd = "";
                string FileName = "";
                tempAdd = "SoTheoDoiCapPhepLaiXe.xlsx";
                FileName = string.Format("SoTheoDoiCapPhepLaiXe_{0}{1}", DateTime.Now.ToShortDateString().Replace("/", "_"), ".xlsx");
                try
                {
                    FileInfo template = new FileInfo(Server.MapPath(string.Format(@"\Templates\GPS_Excel\{0}", tempAdd)));
                    FileInfo newFile = new FileInfo(Server.MapPath(@"\Templates\GPS_Excel\CB_DonViHetHanGiayPhepNEW.xlsx"));
                    // Using the template to create the newFile...
                    using (OfficeOpenXml.ExcelPackage excelPackage = new OfficeOpenXml.ExcelPackage(newFile, template))
                    {
                        // Getting the complete workbook...
                        ExcelWorkbook myWorkbook = excelPackage.Workbook;
                        // Getting the worksheet by its name...
                        ExcelWorksheet myWorksheet = myWorkbook.Worksheets["Sheet1"];
                        int recordIndex = 7;
                        foreach (var item in model)
                        {
                            myWorksheet.Cells[recordIndex, 1].Value = item.STT.ToString();
                            myWorksheet.Cells[recordIndex, 2].Value = item.CB_PT_BIENSO == null ? "" : item.CB_PT_BIENSO;
                            myWorksheet.Cells[recordIndex, 3].Value = item.XE_CSDT == null ? "" : item.XE_CSDT;
                            myWorksheet.Cells[recordIndex, 4].Value = item.XE_HOPDONG == null ? "" : item.XE_HOPDONG;
                            myWorksheet.Cells[recordIndex, 5].Value = item.PT_NHANHIEU == null ? "" : item.PT_NHANHIEU;
                            myWorksheet.Cells[recordIndex, 6].Value = item.PT_SOMAY == null ? "" : item.PT_SOMAY;
                            myWorksheet.Cells[recordIndex, 7].Value = item.PT_SOKHUNG == null ? "" : item.PT_SOKHUNG;
                            myWorksheet.Cells[recordIndex, 8].Value = item.PT_SOGPXTL == null ? "" : item.PT_SOGPXTL.ToString();
                            myWorksheet.Cells[recordIndex, 9].Value = item.CB_PT_NGAYGCNKD == null ? "" : item.CB_PT_NGAYGCNKD.ToString();
                            recordIndex++;
                        }
                        if (SearchModel.pDVGTVT_MA != null)
                        {
                            myWorksheet.Cells[1, 1].Value = _db.GetOne<SDONVIGTVT>(x => x.DVGTVT_MA == SearchModel.pDVGTVT_MA).DVGTVT_TEN; ;
                        }
                        excelPackage.Save();
                    }
                    string contentType = string.Empty;
                    string CurrentFileName = Server.MapPath(@"\Templates\GPS_Excel\CB_DonViHetHanGiayPhepNEW.xlsx");
                    if (CurrentFileName.Contains(".xlsx"))
                    {
                        contentType = "application/msexcel";
                    }
                    return File(CurrentFileName, contentType, FileName);
                }
                catch (Exception)
                {
                    throw;
                }

            }
            var pageIndex = SearchModel.Page ?? 1;
            SearchModel.SearchResults = model.ToPagedList(pageIndex, GPS.COMMON.Constants.PageSize);
            return View(SearchModel);
        }
        #endregion

        //#region Detail
        //public ActionResult _Detail(string id)
        //{
        //    string _DVGTVT_MA = id;
        //    CrudModelSDONVIGTVT _model = new CrudModelSDONVIGTVT();
        //    SDONVIGTVT model = _db.GetOne<SDONVIGTVT>(o => o.DVGTVT_MA == _DVGTVT_MA && o.DVGTVT_MAQL == "40");
        //    GPS.COMMON.Helpers.CopyObject<CrudModelSDONVIGTVT>(model, ref _model);
        //    initialCategoryDetailAction(_model);
        //    return PartialView("_Detail", _model);
        //}
        //public void initialCategoryDetailAction(CrudModelSDONVIGTVT view)
        //{
        //    if (view.DVGTVT_ACTIVE == "1")
        //    {
        //        ViewBag.txtTrangThai = "Hiệu Lực";
        //    }
        //    else
        //    {
        //        ViewBag.txtTrangThai = "Không Hiệu Lực";
        //    }
        //    string selectedvalue = _db.Filter<SLOAICSDT>(x => x.LOAICSDT_MA == view.DVGTVT_LOAI).Select(x => x.LOAICSDT_TEN).FirstOrDefault().ToString();
        //    ViewBag.lstLoaiDV = selectedvalue;

        //    string selectedvalueDVGTVT = _db.Filter<SDONVIGTVT>(x => x.DVGTVT_MAQL == view.DVGTVT_MAQL).Select(x => x.DVGTVT_TEN).FirstOrDefault().ToString();
        //    ViewBag.lstDVGTVT = selectedvalueDVGTVT;
        //}
        //#endregion

        //#region"Cấp phép"

        //[HttpGet]
        //public ActionResult _Edit(string id)
        //{
        //    string _DVGTVT_MA = id;
        //    SDONVIGTVT model = _db.GetOne<SDONVIGTVT>(x => x.DVGTVT_MA == _DVGTVT_MA && x.DVGTVT_MAQL == "40");
        //    CrudModelDCPDVGTVT _model = new CrudModelDCPDVGTVT();
        //    _model.DCP_DVGTVT_MA = model.DVGTVT_MA;
        //    _model.DCP_DVGTVT_HANGGPLX = model.DVGTVT_HANGGPLX;
        //    _model.DCP_DVGTVT_MAQL = model.DVGTVT_MAQL;
        //    _model.DCP_DVGTVT_TEN = model.DVGTVT_TEN;
        //    _model.DCP_DVGTVT_TEL = model.DVGTVT_TEL;
        //    _model.DCP_DVGTVT_ADD = model.DVGTVT_ADD;
        //    _model.DCP_SOGP_CU = model.DVGTVT_SOGP;
        //    _model.DCP_NGAYGP_CU = model.DVGTVT_NGAYGP;
        //    _model.DCP_NGAYHHGP_CU = model.DVGTVT_NGAYHHGP;
        //    _model.DCP_SOVB_KT = "";
        //    _model.DCP_DVGTVT_LLDT_CU = model.DVGTVT_LLDT;
        //    return PartialView("_Edit", _model);
        //}

        //[HttpPost]
        //public ActionResult _Edit(CrudModelDCPDVGTVT model, FormCollection form)
        //{

        //    if (ModelState.IsValid)
        //    {

        //        DCPDVGTVT _model = new DCPDVGTVT();
        //        GPS.COMMON.Helpers.CopyObject<DCPDVGTVT>(model, ref _model);
        //        _model.DCP_NGAYNHAP = DateTime.Now;
        //        _model.DCP_NGUOINHAP = "admin";
        //        _model.ID = EFSequence.GetNextValue("SEQ_DLSDONVIGTVT_ID");
        //        _db.Insert<DCPDVGTVT>(_model);
        //        //update trạng thái DGV:
        //        string _DCP_DVGTVT_MA = model.DCP_DVGTVT_MA;
        //        var _dvgtvt = _db.GetOne<SDONVIGTVT>(o => o.DVGTVT_MA == _DCP_DVGTVT_MA && o.DVGTVT_MAQL == "40");
        //        _dvgtvt.DVGTVT_ACTIVE = "1";
        //        _db.Update<SDONVIGTVT>(_dvgtvt);

        //        if (model.ExportWord == "Xuất giấy phép")
        //        {
        //            byte[] contentFile = null;
        //            contentFile = GPS.ENTITYS.EXPORTWORD_DCPDVGTVT.EXPORT_DCPDVGTVT(Server.MapPath("~/Templates/GPS_Word/GP_CoSoDaoTao.docx"), model, "DocxGenericReport");
        //            string nameOutput = "Capphepdaotao_" + _model.DCP_DVGTVT_MA + "_" + DateTime.Now.ToShortDateString() + ".docx";
        //            return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);
        //        }
        //        else
        //        {
        //            string url = Url.Action("Index", "GPS_GiayPhepDaoTao");
        //            return Json(new { success = true, url = url });
        //        }
        //    }
        //    else
        //    {
        //        return PartialView("_Edit", model);
        //    }

        //}
        //#endregion

        //[HttpGet]
        //public ActionResult Export(string id)
        //{
        //    string _DVGTVT_MA = id;
        //    SDONVIGTVT model = _db.GetOne<SDONVIGTVT>(x => x.DVGTVT_MA == _DVGTVT_MA);
        //    CrudModelDCPDVGTVT _model = new CrudModelDCPDVGTVT();
        //    _model.DCP_DVGTVT_MA = model.DVGTVT_MA;
        //    _model.DCP_DVGTVT_HANGGPLX = model.DVGTVT_HANGGPLX;
        //    _model.DCP_DVGTVT_MAQL = model.DVGTVT_MAQL;
        //    _model.DCP_DVGTVT_TEN = model.DVGTVT_TEN;
        //    _model.DCP_DVGTVT_TEL = model.DVGTVT_TEL;
        //    _model.DCP_DVGTVT_ADD = model.DVGTVT_ADD;
        //    _model.DCP_SOGP_CU = model.DVGTVT_SOGP;
        //    _model.DCP_NGAYGP_CU = model.DVGTVT_NGAYGP;
        //    _model.DCP_NGAYHHGP_CU = model.DVGTVT_NGAYHHGP;
        //    _model.DCP_SOVB_KT = "";
        //    _model.DCP_DVGTVT_LLDT_CU = model.DVGTVT_LLDT;

        //    return View("Export", _model);
        //}

        //[HttpPost]
        //public ActionResult Export(CrudModelCAPPHEP_PHUONGTIEN model, FormCollection form)
        //{
        //    if (ModelState.IsValid)
        //    {
        //        DCPDVGTVT _model = new DCPDVGTVT();
        //        GPS.COMMON.Helpers.CopyObject<DCPDVGTVT>(model, ref _model);
        //        _model.DCP_NGAYNHAP = DateTime.Now;
        //        _model.DCP_NGUOINHAP = "admin";
        //        _model.ID = EFSequence.GetNextValue("SEQ_DLSDONVIGTVT_ID");

        //        _db.Insert<DCPDVGTVT>(_model);
        //        _db.CommitTransaction();
        //        //update trạng thái DGV:
        //        string _id1 = model.DCP_DVGTVT_MA;

        //        var _dvgtvt = _db.GetOne<SDONVIGTVT>(o => o.DVGTVT_MA == _id1);
        //        _dvgtvt.DVGTVT_ACTIVE = "1";
        //        _db.Update<SDONVIGTVT>(_dvgtvt);
        //        _db.CommitTransaction();

        //        model.DCP_DVGTVT_MAQL = _db.GetOne<SDONVIGTVT>(x => x.DVGTVT_MA == model.DCP_DVGTVT_MAQL).DVGTVT_TEN;

        //        if (model.ExportWord == "Xuất giấy phép")
        //        {
        //            byte[] contentFile = null;
        //            contentFile = GPS.ENTITYS.EXPORTWORD_DCPDVGTVT.EXPORT_DCPDVGTVT(Server.MapPath("~/Templates/GPS_Word/GP_CoSoDaoTao.docx"), model, "DocxGenericReport");
        //            string nameOutput = "Capphepdaotao_" + _model.DCP_DVGTVT_MA + "_" + DateTime.Now.ToShortDateString() + ".docx";
        //            return File(contentFile, "application/vnd.openxmlformats-officedocument.wordprocessingml.document", nameOutput);
        //        }
        //        else
        //        {
        //            return View("Index");
        //        }

        //    }
        //    else
        //    {
        //        return View("Index");
        //    }
        //}

        public List<CrudModelCAPPHEP_PHUONGTIEN> GetListData(ViewModelCAPPHEP_PHUONGTIEN SearchModel)
        {
            var _pDVGTVT_MA = new OracleParameter("pDVGTVT_MA", OracleDbType.Varchar2, (SearchModel.pDVGTVT_MA == null ? DBNull.Value : (object)SearchModel.pDVGTVT_MA), ParameterDirection.Input);
            var _pRET = new OracleParameter("pRET", OracleDbType.RefCursor, ParameterDirection.Output);
            var entities = _db.DbContext.Database.SqlQuery<CrudModelCAPPHEP_PHUONGTIEN>("BEGIN GPS_CAPPHEP.GPS_CAPPHEP_DSXTL(:pDVGTVT_MA,:pRET); end;"
                                                                            , _pDVGTVT_MA
                                                                            , _pRET).ToList();
            return entities;
        }
    }
}